using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Threading;
using System.Web.Script.Serialization;
using System.Collections.Generic;
using System.Text;
using MBLocale;

//class needed for data fetch
public class DataFetchInfo
{
    public List<target> targets;
    public src source;
}
public class target
{
    public string tb;
    public string fl;
    public string value;
}
public class src
{
    public string pg;
    public string ctrl;
    public string value;
    public string hvalue;
}
//class needed for data fetch

public partial class mb_AjaxHandler : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Request.QueryString["action"] != null)
        {
            string action = Request.QueryString["action"];
            switch (action)
            {
                case "CV":
                    string strResponse = "";
                    string mode = Request.QueryString["mode"] != null ? Request.QueryString["mode"] : "";
                    if (mode.Trim() != "")
                        strResponse = getCategoryResponse(mode, Request);
                    Response.Clear();
                    //Response.CacheControl = "no-cache";
                    Response.Write(strResponse);
                    Response.End();
                    break;
                case "ctrlevnt_fetch":
                    //System.Threading.Thread.Sleep(2000);
                    //handleControlEvents_RelatedDataManage(Request.QueryString["data"].ToString());
                    Response.Write(handleControlEvents_RelatedDataManage(Request.QueryString["data"].ToString()));                    
                    break;
                case "bnd":
                    string strBindRowID = Request.QueryString["bndid"] != null ? Request.QueryString["bndid"].ToString() : "";
                    string value = Request.QueryString["val"] != null ? Request.QueryString["val"].ToString() : "";
                    string limitTo = Request.QueryString["limit"] != null ? "top " + Request.QueryString["limit"].ToString() : "";
                    //System.Threading.Thread.Sleep(2000);
                    Response.Write(getBindData(strBindRowID, value, limitTo));
                    break;
                case "rpt_src_fil": // Added by Imran Gani on 20-Jul-2012, for Source Filter.
                    //System.Threading.Thread.Sleep(2000);
                    Response.Write(getReportNeeds(Request));
                    break;
                case "recently_visited":
                    //System.Threading.Thread.Sleep(3000);
                    Response.Write(GetRecentlyVisitedPages());
                    break;
                case "Quick_links":
                    //System.Threading.Thread.Sleep(3000);
                    Response.Write(GetQuickLinks());
                    break;
                case "data_search": // Added by Imran Gani on 11-Mar-2013, for company wide data search.
                    //System.Threading.Thread.Sleep(2000);
                    Response.Write(doDataSearch(Request));
                    break;
                case "enable_search": // Added by Imran Gani on 09-May-2013, to get settings for records search at side panel.
                    Response.Write(getSearchConfiguration());
                    break;
                case "import_Obj_fields": // Added by Imran Gani on 30-Jul-2013, for import object related fields.
                    Response.Write(getObjectRelatedFields(Request));
                    break;
                case "mail_merge": // Added by Imran Gani on 03-Dec-2013, for mail merger object related email fields.
                    Response.Write(getObjectRelatedEmailFields(Request));
                    break;
            }
            //if (action == "CV")
            //{
            //    //System.Threading.Thread.Sleep(1000);
            //    string strResponse = "";
            //    string mode = Request.QueryString["mode"] != null ? Request.QueryString["mode"] : "";
            //    if (mode.Trim() != "")
            //        strResponse = getCategoryResponse( mode , Request );
            //    Response.Clear();
            //    //Response.CacheControl = "no-cache";
            //    Response.Write(strResponse);
            //    Response.End();
            //}  
        }
    }
    private string getBindData(string strBindRowID,string curvalue,string limitQry)
    {
        security hdds = new security();
        DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            BindControl BC = new BindControl();
            StringBuilder ret = new StringBuilder();
        string outt="";
            try
            {
                string displayField = ""; string storageField = "";
                ds = BC.getBindData(strBindRowID, true, curvalue, out displayField, out storageField,limitQry);
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                    foreach (DataRow dr in dt.Rows)
                    {
                        ret.Append("{ \"label\":\"" + hdds.encodeForJs(dr[displayField].ToString()) + "\", \"value\" : \"" + hdds.encodeForJs(dr[displayField].ToString()) + "\"},");
                    }
                }
                if (ret.Length>0)
                {
                    outt = ret.ToString().TrimEnd(',');
                    outt = "[ " + outt + " ]";
                }
            }
            catch (Exception ee) { }
            finally
            {
                BC = null;
            }            
        return outt;
    }
    //private string serialize(DataTable dt)
    //{
    //    string ret = "";
    //    try
    //    {
            
    //        if (dt.Rows.Count > 0)
    //        {
    //            ret = JsonMethods.ToJson(dt);
    //        }
    //    }
    //    catch (Exception ee) { }
    //    return ret;
    //}
    private string handleControlEvents_RelatedDataManage(string args)
    {
        //args = "{\"targets\": [{\"tb\": \"user setup\", \"fl\": \"last_name\"}],\"source\":{\"pg\":\"p_control_events_test\",\"ctrl\":\"user\",\"value\":\"admin\",\"hvalue\":\"650a333596bbf348\"}}";
        string ret = "";
        JavaScriptSerializer js = new JavaScriptSerializer();
        DataFetchInfo dfi = null;
        try
        {
            dfi = js.Deserialize<DataFetchInfo>(args);
            if (dfi.source.hvalue.Length > 0 || dfi.source.value.Length > 0)
            {
                foreach (target t in dfi.targets)
                {
                    t.value = handleControlEvents_RelatedDatafetch(dfi.source.pg, dfi.source.ctrl, dfi.source.value, dfi.source.hvalue, t);
                }
            }
            ret = js.Serialize(dfi);
        }
        catch (Exception e)
        {
            ret = args;
            LogWriter.WriteLog("Error in handleControlEvents_RelatedDataManage: " + e.Message);
        }
        finally
        {
            js = null; dfi = null;
        }
        return ret;
    }
    private string handleControlEvents_RelatedDatafetch(string sourcePage, string sourceControl, string sourceControlValue, string sourceControlHiddenValue,target trgt)
    {
        //this code does multiple fetches for a single source. This is being done now so that later it can support multiple source-target scenario
        HDDatabase hdd = null; DataSet ds = null; DataSet dsTarget = null; DataRow drTarget = null;
        string ret = "";
        try
        {
            hdd = new HDDatabase();
            ds = hdd.GetDataset("select * from sy_pg_ctr where page_id in (select af_row_id from sy_pg where page_id='" + sourcePage + "') and control_id ='" + sourceControl + "';select * from sy_bnd where af_row_id in (select control_bind_id  from sy_pg_ctr where page_id in (select af_row_id from sy_pg where page_id='" + sourcePage + "') and control_id ='" + sourceControl + "');");
            string query = "";
            query = "select * from " + ds.Tables[1].Rows[0]["bind_table"].ToString() + " where " + ds.Tables[1].Rows[0]["bind_text"].ToString() + "='" + sourceControlValue + "';";//fetch value based on text
            query += "select * from " + ds.Tables[1].Rows[0]["bind_table"].ToString() + " where " + ds.Tables[1].Rows[0]["bind_value"].ToString() + "='" + sourceControlHiddenValue + "';";//fetch value based on value
            dsTarget = hdd.GetDataset(query);
            if (dsTarget.Tables[0].Rows.Count == 1)
                drTarget = dsTarget.Tables[0].Rows[0];
            else if (dsTarget.Tables[1].Rows.Count >0)
                drTarget = dsTarget.Tables[1].Rows[0];
            if (drTarget.Table.Columns.Contains(trgt.fl))
                ret = drTarget[trgt.fl].ToString();
        }
        catch (Exception ee)
        {
            LogWriter.WriteLog("Error in handleControlEvents_RelatedDatafetch: " + ee.Message);
        }
        finally
        {
            hdd = null; ds = null; dsTarget = null; drTarget = null;
        }
        return ret;
    }
    private string GetQuickLinks()
    {
        string jsonstring = "";
        DataTable dt_QuickLinks = new DataTable();
        security se = new security();
        HDDatabase Hdd = new HDDatabase();
        try
        {
            dt_QuickLinks = se.getQuickLinks();
            if (dt_QuickLinks != null)
            {
                for (int i = 0; i < dt_QuickLinks.Rows.Count; i++)
                {
                    if (dt_QuickLinks.Rows[i]["parent_link"].ToString() != "")
                    {
                        DataRow[] row=dt_QuickLinks.Select("af_row_id='"+dt_QuickLinks.Rows[i]["parent_link"].ToString()+"'","");
                        dt_QuickLinks.Rows[i]["parent_link"] = row[0]["link_name"];
                    }
                }
            }
            string[] Removecol = { "created_by", "created_at", "modified_at", "modified_by", "owner_id", "quick_link", "is_accessible", "af_row_id1", "modified_at1", "modified_by1", "owner_id1", "created_at1", "created_by1", "role_id" };
            foreach (string colName in Removecol)
            {
                dt_QuickLinks.Columns.Remove(colName);
            }
            CommonFunctions.encodeData(dt_QuickLinks);
            JavaScriptSerializer js = new JavaScriptSerializer();
            jsonstring = js.Serialize(JsonMethods.ToJson(dt_QuickLinks)).ToString();

        }
        catch (Exception ee)
        {
        }
        return jsonstring;
    }
    private string GetRecentlyVisitedPages()
    {
        string jsonstring = "";
        DataTable dt_LastAccessed = new DataTable();
        security se = new security();
        HDDatabase Hdd = new HDDatabase();
        try
        {
            dt_LastAccessed = se.getLastAccessedMenu();
            dt_LastAccessed.Columns.Add("Image");
            dt_LastAccessed.Columns.Add("NavigatePage");          
            dt_LastAccessed.AcceptChanges();
            
            string defaultIcon = "Default.PNG";

            for (int i=0;i<dt_LastAccessed.Rows.Count;i++)
            {
                if (se.getRightsForMRUObjects(dt_LastAccessed.Rows[i]["pageset_id"].ToString(), "read"))
                {
                    //string pagesetafrowid = Hdd.GetColumnValue("sy_pgs", "af_row_id", "pageset_id = '" + dt_LastAccessed.Rows[i]["pageset_id"].ToString() + "'");
                    string page_id = Hdd.GetColumnValue("sy_pgs_mem", "page_id", "pageset_id='" + dt_LastAccessed.Rows[i]["pageset_id"].ToString() + "'");
                    string iconname = Hdd.GetColumnValue("sy_pg", "page_icon", "page_id = '" + page_id + "'");
                    if (iconname != "")
                        dt_LastAccessed.Rows[i]["Image"] = iconname.ToLower();
                    else
                        dt_LastAccessed.Rows[i]["Image"] = defaultIcon;
                    String navigatePage = se.getMenuNavigateUrl(dt_LastAccessed.Rows[i]);
                    if (navigatePage.Contains("?"))
                    {
                        string[] ng = navigatePage.Split('?');
                        dt_LastAccessed.Rows[i]["NavigatePage"] = ng[0];
                    }
                }
                else
                {
                    dt_LastAccessed.Rows[i].Delete();
                    //dt_LastAccessed.AcceptChanges();
                }

            }
            dt_LastAccessed.AcceptChanges(); // Added by 'Imran Gani' on 28-Jan-2013, for icon issue if object do not have access.
            dt_LastAccessed.Columns.Remove("menu_seq");
            dt_LastAccessed.Columns.Remove("menu_click");
            dt_LastAccessed.Columns.Remove("parent_menu_id");
            dt_LastAccessed.Columns.Remove("pageset_url");
            JavaScriptSerializer js = new JavaScriptSerializer();
            jsonstring = js.Serialize(JsonMethods.ToJson(dt_LastAccessed)).ToString();


        }
        catch (Exception ee)
        {
            LogWriter.WriteLog("Error in handle Recently Visited pages: " + ee.Message);
        }
        finally
        {
            se = null;
            Hdd = null;
        }
        return jsonstring;
    }
    private string getCategoryResponse(string mode, System.Web.HttpRequest RequestObj)
    {
        string strpagedetails = RequestObj.QueryString["pagedetails"] != null ? RequestObj.QueryString["pagedetails"].ToString() : "";
        string strCategorygroupingField = RequestObj.QueryString["CategoryField"] != null ? RequestObj.QueryString["CategoryField"].ToString() : "";
        string strCategoryStorageValue = RequestObj.QueryString["CategoryStorageValue"] != null ? RequestObj.QueryString["CategoryStorageValue"].ToString() : "";
        string strPagesetRowID = RequestObj.QueryString["PageSetId"] != null ? RequestObj.QueryString["PageSetId"].ToString() : "";
        string strJsonResponse = "";
        CategoryView cv = new CategoryView();
        if (mode == "CVI") //More Click on Category Items 
        {
            strJsonResponse = cv.getCategoryItemsJSONStr(strPagesetRowID, strpagedetails, strCategorygroupingField, strCategoryStorageValue);
        }
        else if (mode == "CVH") //More Click On Category Header
        {
            strJsonResponse = cv.getCategoryHeaderJSONStr(strPagesetRowID, strpagedetails, strCategorygroupingField);
        }
        else
        {
            return "{\"Data\":\"No Records\"}";
        }
        return strJsonResponse;
    }

    /// <summary>
    /// To get the related field values for report source filter.
    /// </summary>
    /// <param name="Request">URL of report source filter.</param>
    /// <returns>Json String of field values.</returns>
    private string getReportNeeds(System.Web.HttpRequest Request) // Added by Imran Gani on 20-Jul-2012, for Source Filter.
    {
        HDDatabase hdd = new HDDatabase();
        security hdds = new security();
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        BindControl BC = new BindControl();
        StringBuilder sbJson = new StringBuilder();
        string ret = "", sql = "";
        GenerateGrid gg = new GenerateGrid(); // Added by Imran Gani on 28-Aug-2012, for DataTime format.
        Localization ll = new Localization();
        try
        {
            string data = Request.QueryString["data"] != null ? Request.QueryString["data"].ToString() : "";
            string sVal = Request.QueryString["val"] != null ? Request.QueryString["val"].ToString() : ""; // Added by Imran Gani on 25-Sep-2012, for current value of autocomplete.
            if (data != "")
            {

                DataRow drFld = hdd.GetTableRow("select * from sy_pg_ctr where af_row_id='" + data + "';");
                //if (drFld["control_attrib_type"].ToString() == "DA" || drFld["control_attrib_type"].ToString() == "DT") // Added by Imran Gani on 28-Sep-2012, to handle date/time field.
                //{
                //    ret = "[ { \"label\":\" Ex : " + ll.getDateFormat().ToUpper() + "\", \"value\" : \"\" } ]";
                //}
                //else
                //{
                string sTable = hdd.GetColumnValue("select page_table_name from sy_pg where af_row_id=(select page_id from sy_pg_ctr where af_row_id='" + data + "')");
                if (drFld["control_bind_id"].ToString() != "")
                    sql = BC.GetSearchSQLBySearchValue(drFld["control_bind_id"].ToString(), sVal, true, true, ""); // Added by Imran Gani on 25-Sep-2012, for autocomplete-bind related query.
                //sql = BC.GetBindSql(drFld["control_bind_id"].ToString());

                else
                    sql = "select distinct [" + drFld["control_id"].ToString() + "], [" + drFld["control_id"].ToString() + "] from " + sTable + " where [" + drFld["control_id"].ToString() + "] like '%" + sVal + "%'";
                dt = hdd.GetDataTable(sql);
                foreach (DataRow dr in dt.Rows)
                {
                    //if (drFld["control_attrib_type"].ToString() == "DA" || drFld["control_attrib_type"].ToString() == "DT")
                    //    sbJson.Append("{ \"label\":\"" + gg.manageDateTimeValues(drFld["control_attrib_type"].ToString().ToLower(), HttpUtility.HtmlEncode(dr[0].ToString())) + "\", \"value\" : \"" + HttpUtility.HtmlEncode(dr[1].ToString()) + "\"},");
                    //else
                    //    sbJson.Append("{ \"label\":\"" + HttpUtility.HtmlEncode(dr[0].ToString()) + "\", \"value\" : \"" + HttpUtility.HtmlEncode(dr[1].ToString()) + "\"},");
                    sbJson.Append("{ \"label\":\"" + hdds.encodeForJs(dr[1].ToString()) + "\", \"id\" : \"" + hdds.encodeForJs(dr[0].ToString()) + "\"},"); // Added by Imran Gani on 25-Sep-2012, for autocomplete.
                }
                if (sbJson.Length > 0)
                {
                    ret = sbJson.ToString().TrimEnd(',');
                    ret = "[ " + ret + " ]";
                }
                //}
            }
        }
        catch (Exception ee)
        {
            LogWriter.WriteLog("Error in AjaxHandler: getReportNeeds: " + ee.Message);
        }
        finally
        {
            hdd = null; ds.Dispose(); dt.Dispose(); BC = null; sbJson = null; sql = string.Empty; gg = null; ll = null;
        }
        return ret;
    }

    /// <summary>
    /// To get the related objects of records for search key.
    /// </summary>
    /// <param name="Request">URL of Data search.</param>
    /// <returns>Json String of field values.</returns>
    private string doDataSearch(System.Web.HttpRequest Request) // Added by Imran Gani on 11-Mar-2013, for company wide data search.
    {
        HDDatabase hdd = new HDDatabase();
        security hdds = new security();
        PageInfoLoad PIL = new PageInfoLoad();
        GenerateGrid objGG = new GenerateGrid();
        GridPaging GP = new GridPaging();
        CategoryView cv = new CategoryView();
        Hashtable hsDatatype = new Hashtable();

        bool bRead = false, bCreate = false, bEdit = false, bDelete = false, blnActions = false, bIsCombinedOrderBy = false;
        string sFieldAlias = "", sCombinedOrderBy = "", Order = " desc", QuickSearchSQL = "", iPagingDetails = "";

        try
        {
            string searchString = Request.QueryString["data"] != null ? HttpUtility.HtmlDecode(Request.QueryString["data"].ToString()) : "";
            string sVal = Request.QueryString["val"] != null ? Request.QueryString["val"].ToString() : "";
            string strpagedetails = Request.QueryString["pgInfo"] != null ? Request.QueryString["pgInfo"].ToString() : "";
            string strPagesetRowId = Request.QueryString["pgs_id"] != null ? Request.QueryString["pgs_id"].ToString() : "";
            string strMenuRowId = Request.QueryString["mn"] != null ? Request.QueryString["mn"].ToString() : "";
            
            if (strPagesetRowId == "")
                strPagesetRowId = hdd.GetColumnValue("select pageset_id from sy_pgs_mem where object_id in (select object_id from sy_obj where af_row_id = '" + sVal + "')");
            if (strMenuRowId == "")
                strMenuRowId = hdd.GetColumnValue("select af_row_id from sy_mnu where is_active = 1 and pageset_id = '" + strPagesetRowId + "'");
            string strEditPage = objGG.getEditPage(hdd, strMenuRowId);
            DataSet dsePageObj = PIL.GetPageSetPagesSrchInfo(strPagesetRowId);
            DataTable dtaPageSet = dsePageObj.Tables["PageSet"];
            DataTable dtaPage = dsePageObj.Tables["Page"];
            DataTable dtaPageKeys = dsePageObj.Tables["PageKeys"];
            DataTable dtaPageSearch = dsePageObj.Tables["PageSrch"];
            DataTable dtaPageControls = dsePageObj.Tables["Page Controls"];

            string strObjectSearchSql = objGG.getObjectSearchSQL(dtaPageSearch, dtaPageControls, dtaPage, searchString);
            if (Request.QueryString["flmode"] != null)
                if (Request.QueryString["flmode"].ToString() == "1")
                    strObjectSearchSql = getFilterSearchSql(dtaPageSearch);

            string iTotalRowFetchCount = "0";
            string iTotalRecordFetchCount = "0";
            string iCurrentRecordCount = "0";
            string iNoofRecordsLimit = "0";
            string ipageCount = "0";
            bool Applypaging = false;
            string[] pagearrVal = strpagedetails.Split('|');
            Int64 iNewPageCount = 0;
            if (pagearrVal.Length > 1)
            {
                iTotalRecordFetchCount = pagearrVal[0];
                iCurrentRecordCount = pagearrVal[1];
                iNoofRecordsLimit = pagearrVal[2];
                ipageCount = pagearrVal[3];

                iNewPageCount = Convert.ToInt64(ipageCount);
                iNewPageCount = iNewPageCount + 1;
                Applypaging = true;
            }
            else
                Applypaging = false;

            Int64 ICurrentRecordValue = 0;
            if (Applypaging)
            {
                if (Convert.ToInt64(iCurrentRecordCount) > Convert.ToInt64(iNoofRecordsLimit))
                    ICurrentRecordValue = Convert.ToInt64(iCurrentRecordCount) - Convert.ToInt64(iNoofRecordsLimit);
                else
                    return "{\"NORECORD\":\"No Additional Records Available\"}";
            }
            string sOrderby = "modified_at";
            string sNoofRecords = hdd.getAcrMemValue("3813ac7202378893", "1"); // to set the number of records displayed
            if (sNoofRecords != "" && sNoofRecords != "0")
                GlobalValues.intNoOfRecordsPerPage = int.Parse(sNoofRecords);
            else
                GlobalValues.intNoOfRecordsPerPage = 8;

            DataRow drAction = null;
            if (dsePageObj.Tables.Contains("Page Actions") && dsePageObj.Tables["Page Actions"] != null)
                if (dsePageObj.Tables["Page Actions"].Rows.Count > 0)
                {
                    drAction = dsePageObj.Tables["Page Actions"].Select()[0];
                    blnActions = true;
                }

            iTotalRowFetchCount = ICurrentRecordValue.ToString();

            string condstr = strObjectSearchSql.StartsWith("  and ") == true ? strObjectSearchSql.Substring(5) : strObjectSearchSql;
            string strSql = cv.getCategoryRowFetchQuery(dtaPage, dtaPageKeys, dtaPageSearch, ref  dtaPageControls, ref  hsDatatype, condstr, ref  sFieldAlias, null, QuickSearchSQL, ref  bIsCombinedOrderBy, sOrderby, ref  sCombinedOrderBy, ref iTotalRowFetchCount, Applypaging);
            
            if (strSql != "")
            {
                strSql += strObjectSearchSql;

                #region Added by 'Imran Gani' on 14-Mar-2014, to use ROW_NUMBER() key in query with starting and ending index.

                int iStartCount = (Int32.Parse(ipageCount) * GlobalValues.intNoOfRecordsPerPage);
                int iEndCount = iStartCount + GlobalValues.intNoOfRecordsPerPage;
                iStartCount++;
                strSql = "Select " + sFieldAlias + " from ( " + strSql + ") as tt  where [row] between " + iStartCount + " and " + iEndCount + " order by [row] asc";
                #endregion
                DataSet dseObj = hdd.GetDataset(strSql);

                if (dseObj.Tables.Count == 0)
                    return "{\"NORECORD\":\"No Additional Records Available\"}";
                else
                    GP.FillBindAndMasterRelatedValues(dseObj, dtaPage, dtaPageSearch, dtaPageKeys);

                DataTable dtData = dseObj.Tables[0].Clone();
                foreach (DataColumn dc in dtData.Columns)
                {
                    dc.DataType = System.Type.GetType("System.String");
                }
                foreach (DataRow dr in dseObj.Tables[0].Rows)
                {
                    dtData.ImportRow(dr);
                }
                objGG.HandleControlDataType(strPagesetRowId, dtData, hsDatatype, dsePageObj);

                dtData.TableName = "DATA";

                if (Applypaging)
                    iPagingDetails = iTotalRecordFetchCount + "|" + ICurrentRecordValue.ToString() + "|" + iNoofRecordsLimit + "|" + iNewPageCount.ToString(); //TOtalCount | currentCount | limitCount | pagno
                else
                    iPagingDetails = iTotalRowFetchCount + "|" + iTotalRowFetchCount + "|" + GlobalValues.intNoOfRecordsPerPage.ToString() + "|" + "1"; //TOtalCount | currentCount | limitCount | pagno

                hdds.getPagesetRights(strPagesetRowId, ref bRead, ref bCreate, ref bEdit, ref bDelete);
                DataTable dtPageAccess = new DataTable();
                dtPageAccess.Columns.Add("READ", typeof(string));
                dtPageAccess.Columns.Add("CREATE", typeof(string));
                dtPageAccess.Columns.Add("EDIT", typeof(string));
                dtPageAccess.Columns.Add("DELETE", typeof(string));
                dtPageAccess.Columns.Add("ACTION", typeof(string));
                dtPageAccess.Rows.Add(bRead.ToString(), bCreate.ToString(), bEdit.ToString(), bDelete.ToString(), blnActions.ToString());
                dtPageAccess.TableName = "PAGEACCESS";

                DataTable dtPagedetails = new DataTable();
                dtPagedetails.Columns.Add("PAGEDETAILS", typeof(string));
                dtPagedetails.Columns.Add("TOTCOUNT", typeof(string));
                dtPagedetails.Columns.Add("PAGESETID", typeof(string));
                dtPagedetails.Columns.Add("MENUID", typeof(string));
                dtPagedetails.Columns.Add("EDITPAGE", typeof(string));
                dtPagedetails.Rows.Add(iPagingDetails, iTotalRecordFetchCount, strPagesetRowId, strMenuRowId, strEditPage);
                dtPagedetails.TableName = "PAGEINFO";

                DataTable dtDataType = new DataTable();
                dtDataType = cv.getDataTypeTable(hsDatatype, dseObj.Tables[0]);
                dtDataType.TableName = "SCHEMA";

                DataTable dtActionBtn = new DataTable();
                if (blnActions)
                {
                    dtActionBtn.Columns.Add("name", typeof(string));
                    dtActionBtn.Columns.Add("dsc", typeof(string));
                    dtActionBtn.Columns.Add("url", typeof(string));

                    dtActionBtn.Rows.Add(drAction["name"].ToString(), drAction["description"].ToString(), drAction["target_url"].ToString());
                    dtActionBtn.TableName = "ACTION";
                }

                JavaScriptSerializer js = new JavaScriptSerializer();
                DataSet dsresponse = new DataSet();
                dsresponse.Tables.Add(dtData.Copy());
                dsresponse.Tables.Add(dtDataType);
                dsresponse.Tables.Add(dtPageAccess);
                dsresponse.Tables.Add(dtPagedetails);
                if (blnActions) dsresponse.Tables.Add(dtActionBtn);
                return js.Serialize(JsonMethods.ToJson(dsresponse)).ToString();
            }
            else
                return "{\"NORECORD\":\"You do not have sufficient access permission.\"}";
        }
        catch (Exception ee)
        {
            LogWriter.WriteLog("Error in AjaxHandler: doDataSearch: " + ee.Message);
        }
        finally
        {
            hdd = null;
            hdds = null;
            PIL = null;
            objGG = null;
            GP = null;
            cv = null;
            hsDatatype = null;
        }
        return "";
    }

    private string getSearchConfiguration() // Implemented by Imran Gani on 09-May-2013, to get settings for records search at side panel.
    {
        HDDatabase Hdd = new HDDatabase();
        string sReturn = "";
        try
        {
            sReturn = Hdd.GetColumnValue("select mem_value1 from sy_acr_mem where acr_id = 'd6a3bb5766cd8992' and af_row_id = 'a09dd0a6266ef244';");
        }
        catch (Exception ee)
        {
            LogWriter.WriteLog("Error in AjaxHandler: getSearchConfiguration: " + ee.Message);
        }
        finally
        { Hdd = null; }
        return sReturn.ToLower();
    }

    /// <summary>
    /// To get the object related fields.
    /// </summary>
    /// <param name="Request">URL</param>
    /// <returns>Json</returns>
    // Added by 'Imran Gani' on 30-Jul-2013, to get object related fields for avoiding duplicates during import.
    private string getObjectRelatedFields(System.Web.HttpRequest Request)
    {
        HDDatabase hdd = new HDDatabase();
        DataTable dtObjAtt = new DataTable();
        StringBuilder sbJson = new StringBuilder();
        string ret = "";
        try
        {
            string sObjectID = Request.QueryString["val"] != null ? Request.QueryString["val"].ToString() : "";
            DataRow dr = hdd.GetTableRow("select * from sy_acr_mem where af_row_id = '6df49e103aa6a359';");
            if (Convert.ToBoolean(dr["mem_value3"].ToString()))
                sbJson.Append("{ \"label\":\"" + dr["mem_value1"].ToString() + "\", \"id\" : \"" + dr["mem_value2"].ToString() + "\"},");
            dtObjAtt = hdd.GetDataTable("select attrib_name, af_row_id from sy_obj_att where object_id = '" + sObjectID + "' and attrib_id not in ('af_row_id', 'created_by', 'owner_id', 'created_at', 'modified_at', 'modified_by') order by attrib_seq;");
            if (dtObjAtt != null && dtObjAtt.Rows.Count > 0)
            {
                foreach (DataRow drFlds in dtObjAtt.Rows)
                {
                    sbJson.Append("{ \"label\":\"" + drFlds["attrib_name"].ToString() + "\", \"id\" : \"" + drFlds["af_row_id"].ToString() + "\"},");
                }
            }
            if (sbJson.Length > 0)
            {
                ret = sbJson.ToString().TrimEnd(',');
                ret = "[ " + ret + " ]";
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error in AjaxHandler : getObjectRelatedFields " + ex.Message);
        }
        finally
        {
            hdd = null;
            if (dtObjAtt != null)
                dtObjAtt.Dispose();
        }
        return ret;
    }

    /// <summary>
    /// To get the object related fields.
    /// </summary>
    /// <param name="Request">URL</param>
    /// <returns>Json</returns>
    // Added by Imran Gani on 03-Dec-2013, for mail merger object related email fields from where user can select email id.
    private string getObjectRelatedEmailFields(System.Web.HttpRequest Request)
    {
        HDDatabase hdd = new HDDatabase();
        DataTable dtObjAtt = new DataTable();
        StringBuilder sbJson = new StringBuilder();
        string ret = "", sObjectType = "";
        try
        {
            string sObjectID = Request.QueryString["data"] != null ? Request.QueryString["data"].ToString() : "";
            string[] str = sObjectID.Split('@');
            if(str.Length!=0)
            {
                sObjectType = str[0];
                sObjectID = str[1];
            }
            if (sObjectType.ToLower() == "object")
            {
                dtObjAtt = hdd.GetDataTable("select attrib_name, af_row_id from sy_obj_att where object_id = '" + sObjectID + "' and attrib_id not in ('af_row_id', 'created_by', 'owner_id', 'created_at', 'modified_at', 'modified_by') order by attrib_seq;");
                if (dtObjAtt != null && dtObjAtt.Rows.Count > 0)
                    foreach (DataRow drFlds in dtObjAtt.Rows)
                        sbJson.Append("{ \"label\":\"" + drFlds["attrib_name"].ToString() + "\", \"id\" : \"" + drFlds["af_row_id"].ToString() + "\"},");
            }
            if (sObjectType.ToLower() == "sql")
            {
                string Sql = hdd.GetColumnValue("Select report_base_sql from sy_report_sql where af_row_id = '" + sObjectID + "';");
                dtObjAtt = hdd.GetDataTable(Sql);
                if (dtObjAtt != null && dtObjAtt.Columns.Count > 0)
                    foreach (DataColumn dcFlds in dtObjAtt.Columns)
                        sbJson.Append("{ \"label\":\"" + dcFlds.ColumnName + "\", \"id\" : \"" + dcFlds.ColumnName + "\"},");
            }
            if (sbJson.Length > 0)
            {
                ret = sbJson.ToString().TrimEnd(',');
                ret = "[ " + ret + " ]";
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error in AjaxHandler : getObjectRelatedFields " + ex.Message);
        }
        finally
        {
            hdd = null;
            if (dtObjAtt != null)
                dtObjAtt.Dispose();
        }
        return ret;
    }

    private string getFilterSearchSql(DataTable dtaPageSrch) // Added by 'Imran Gani' on 24-Sep-2014, to get the search query of filter
    {
        HDDatabase HDD = new HDDatabase();
        GenerateGrid GG = new GenerateGrid();
        string strQuickSearchSql = "";
        try
        {
            DataRow[] dr_PageQuickSrch = dtaPageSrch.Select("quick_search=1", "search_seq");
            for (int j = 0; j < dr_PageQuickSrch.Length; j++)
            {
                string strAttribId = dr_PageQuickSrch[j]["search_attrib_id"].ToString();
                string strPageId = dr_PageQuickSrch[j]["page_id"].ToString();
                string strBindRowID = dr_PageQuickSrch[j]["control_bind_id"].ToString();
                string strQuickSearchValue = (Request.QueryString[strPageId + "_" + strAttribId] != null ? Request.QueryString[strPageId + "_" + strAttribId].ToString() : null);

                if (strQuickSearchValue != null)
                {
                    string sBindStoreField = "";
                    if (strBindRowID != "")
                    {
                        DataRow drBind = HDD.GetTableRow("select * from sy_bnd where af_row_id = '" + strBindRowID + "';");
                        string sBindDisplayField = "", sBindTable = "";
                        if (drBind != null)
                        {
                            sBindStoreField = drBind["bind_value"].ToString();
                            sBindDisplayField = drBind["bind_text"].ToString();
                            sBindTable = drBind["bind_table"].ToString();
                        }
                    }
                    if (strQuickSearchValue == null)
                        strQuickSearchValue = "";

                    if (strQuickSearchValue != "" && strQuickSearchValue != "--All--")
                    {
                        switch (dr_PageQuickSrch[j]["control_attrib_type"].ToString().ToLower())
                        {
                            case "dt":
                            //Localization ll = new Localization();
                            //strQuickSearchValue = ll.getDateTime4TimeZone(strQuickSearchValue, sUTC_Offset, true, false);
                            //ll = null;
                            //break;
                            case "da":
                                strQuickSearchValue = MBLocalization.getDateValue(HttpContext.Current.Session["userlocale"].ToString(), strQuickSearchValue);
                                break;
                            case "cu":
                            //strQuickSearchValue = changeCurrencyToDBValue(strQuickSearchValue, currencyCode_Rate);
                            //break;
                            case "dc":
                            case "nu":
                            case "an":
                                strQuickSearchValue = MBLocalization.getNumericValue(HttpContext.Current.Session["userlocale"].ToString(), strQuickSearchValue).ToString();
                                break;
                        }
                    }

                    if (strQuickSearchValue != null && strQuickSearchValue != "" && strQuickSearchValue != GlobalValues.ShowAll)
                    {
                        if (strAttribId.ToLower() == "created_by" || strAttribId.ToLower() == "modified_by" || strAttribId.ToLower() == "owner_id")
                        {
                            string sUserDisplayName = GG.getUserDisplayName();
                            strQuickSearchSql += " and [" + strPageId + "].[" + strAttribId + "]='" + HDD.GetColumnValue("select af_row_id from sy_usr where " + sUserDisplayName + "='" + strQuickSearchValue + "'") + "'";
                        }
                        else
                            strQuickSearchSql += " and [" + strPageId + "].[" + strAttribId + "]='" + strQuickSearchValue.Replace("'", "''") + "'";
                    }

                    if (strQuickSearchValue == "")
                        strQuickSearchSql += " and [" + strPageId + "].[" + strAttribId + "] is null";
                }
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error in AjaxHandler : getObjectRelatedFields " + ex.Message);
        }
        finally
        {
            HDD = null; GG = null;
        }
        return strQuickSearchSql;
    }
}
